<?php
namespace app\finance\controller;
set_time_limit(0);
use think\Db;
class Goodsdiff extends Base
{
    public function index()
    {
    	$iw=Request()->param();

        $start_time=isset($iw['start_time'])?strtotime($iw['start_time']):'';
        $end_time=isset($iw['end_time'])?strtotime($iw['end_time']):'';

        if($end_time){
            $_end_time=getOneDay($end_time);
            // $_end_time=$_end_time['et'];
            if ($end_time==$_end_time['st']) {
                $end_time=$_end_time['et'];
            }
        }else{
            $end_time=getOneDay();
            $end_time=$end_time['et'];
        }

        if ($start_time) {
            // $start_time=getOneDay($start_time);
            // $start_time=$start_time['st'];
        }else{
            $start_time=getDayRegion($end_time,1);
            $start_time=$start_time['st'];
        }
        
        $iw['start_time']=date('Y-m-d H:i:s',$start_time);
        $iw['end_time']=date('Y-m-d H:i:s',$end_time);
        if ($iw['selt']!=='0') {
            $iw['selt']=$iw['selt']?:1;
        }

		$purchase = Db::field('pg.*,IFNULL(sval.price,0) as shop_price,p.employee_name')
        ->table('sdzx_purchase_goods')->alias('pg')
        ->join('sdzx_purchase p','pg.purchase_id=p.purchase_id')
        ->join('sdzx_goods_specval sval','pg.specval_id=sval.specval_id','left')
		->where("pg.price != pg.cur_price and pg.cur_price != 0 and pg.create_time between '{$iw['start_time']}' and '{$iw['end_time']}'")
		->select();
		
        $Tcmp=0;$Tpp=0;$Tpur=0;$TEdif=0;$Tpnum=0;$Tdif=0;
        foreach ($purchase as $k => $v) {
            $Tcmp += $v['shop_price']; //商城价
            $Tpp += $v['price']; // 下单时价格
            $Tpur += $v['cur_price']; //采购价
            $Tpnum += $v['real_num']; //采购数量
            $TEdif += $v['cur_price']-$v['price']; //单品差价合计
            $Tdif += ($v['cur_price']-$v['price'])*$v['real_num']; //总差价 
        }

        $data['w'] = $iw;
        $data['purchase'] =$purchase;
        $data['Tcmp']     = $Tcmp;
        $data['Tpp']      = $Tpp;
        $data['Tpur']     = $Tpur;
        $data['Tpnum']    = $Tpnum;
        $data['TEdif']    = $TEdif;
        $data['Tdif']     = $Tdif;
		return $this->fetch('',$data);
    }

    function statistics(){
        $iw=Request()->param();

        $start_time=isset($iw['start_time'])?strtotime($iw['start_time']):'';
        $end_time=isset($iw['end_time'])?strtotime($iw['end_time']):'';

        if($end_time){
            $_end_time=getOneDay($end_time);
            // $_end_time=$_end_time['et'];
            if ($end_time==$_end_time['st']) {
                $end_time=$_end_time['et'];
            }
        }else{
            $end_time=getOneDay();
            $end_time=$end_time['et'];
        }

        if ($start_time) {
            // $start_time=getOneDay($start_time);
            // $start_time=$start_time['st'];
        }else{
            $start_time=getDayRegion($end_time,1);
            $start_time=$start_time['st'];
        }
        
        $iw['start_time']=date('Y-m-d H:i:s',$start_time);
        $iw['end_time']=date('Y-m-d H:i:s',$end_time);
        if ($iw['selt']!=='0') {
            $iw['selt']=$iw['selt']?:1;
        }


        $list = Db::field('*')
        ->table('sdzx_order')->alias('o')
        ->join('sdzx_order_goods g','o.order_id=g.order_id')
        // ->where("o.order_id in({$ids})")
        ->where(['pay_status'=>1])
        ->where('o.pay_time','between',[$iw['start_time'],$iw['end_time']])
        ->select();

        $arr = [];
        foreach ($list as $k => $v) {
            if (!isset($arr[$v['order_id']]['order_id'])) {
                $arr[$v['order_id']] = [
                    'order_id'        => $v['order_id'],
                    'order_no'        => $v['order_no'],
                    'user_id'         => $v['user_id'],
                    'pay_time'        => $v['pay_time'],
                    'receiver_name'   => $v['receiver_name'],
                    'receiver_mobile' => $v['receiver_mobile'],
                    'order_money'     => $v['order_money'],
                    'pay_money'       => $v['pay_money'],
                    'list' => ['num'=>0]
                ];
            }
            
            $ptmp = Db::name('purchase_goods')->where(['specval_id'=>$v['goods_specval_id'],'status'=>1])->order('pg_id desc')->find();
            $v['pur_price'] = $ptmp['cur_price']?:0;

            $v['price_total'] = $v['price']*$v['num'];
            $v['cost_total'] = $v['pur_price']*$v['num'];
            $v['diff_total'] = $v['price_total']-$v['cost_total'];

            $arr[$v['order_id']]['list']['num'] +=1;
            if ($v['diff_total']>0) {
                $arr[$v['order_id']]['list']['raise_total'] += $v['diff_total'];
                $arr[$v['order_id']]['list']['raise'][] = $v;
            }else{
                $arr[$v['order_id']]['list']['loss_total'] += $v['diff_total'];
                $arr[$v['order_id']]['list']['loss'][] = $v;
            }
            $arr[$v['order_id']]['diff_total'] += $v['diff_total'];
        }

        foreach ($arr as $k => $v) {
            // $v['change'] = Db::name('order_change')->where(['order_id'=>$v['order_id'],'pay_status'=>1])->select();
            $tmp = Db::field('sum(price)')->name('order_change')->where(['order_id'=>$v['order_id'],'pay_status'=>1])->value('sum(price)');

            $v['refundgoods'] = Db::field('sum(rg_num*return_money)')->name('order_goods_return')->where(['order_id'=>$v['order_id'],'pay_status'=>1])->value('sum(rg_num*return_money)');
            $v['balance'] = $v['pay_money']+($tmp-$v['refundgoods']);
            $v['agent'] = Db::field("e.name")->table('sdzx_employee')->alias('e')->join('sdzx_shop s','e.id=s.agent_id','left')->where("s.user_id={$v['user_id']}")->value("e.name");

            $arr[$k] = $v;
        }

        vendor("PHPExcel.PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.IOFactory");
        vendor('PHPExcel.PHPExcel.PHPExcel_Style_Alignment');

        $PHPExcel = new \PHPExcel(); //实例化PHPExcel类，类似于在桌面上新建一个Excel表格

        $PHPSheet = $PHPExcel->getActiveSheet(); //获得当前活动sheet的操作对象
        $PHPSheet->setTitle(date('Y-m-d')); //给当前活动sheet设置名称

        // $PHPSheet->mergeCells('A1:N1');
        // $PHPSheet->setCellValue('A1','订单明细导出（'.date('Y-m-d').'）');//
        // // $PHPSheet->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // $PHPSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        // $PHPSheet->getStyle('A1')->getFont()->setSize(20)->setBold(true);

        $PHPSheet->mergeCells('A1:H1');
        $PHPSheet->setCellValue('A1','订单信息');//
        $PHPSheet->getStyle('A1')->getFont()->setBold(true);
        $PHPSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $PHPSheet->mergeCells('I1:L1');
        $PHPSheet->setCellValue('I1','采购信息');//
        $PHPSheet->getStyle('I1')->getFont()->setBold(true);
        $PHPSheet->getStyle('I1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $PHPSheet->setCellValue('A2','下单时间')->setCellValue('B2','收件人')->setCellValue('C2','商品名称')->setCellValue('D2','价格')->setCellValue('E2','数量')->setCellValue('F2','小计')->setCellValue('G2','总金额')->setCellValue('H2','管家')->setCellValue('I2','进价')->setCellValue('J2','数量')->setCellValue('K2','总价')->setCellValue('L2','差价（=小计-总价）')->setCellValue('M2','差价小计')->setCellValue('N2','差价总额');

        $n = 3;
        foreach ($arr as $k => $v) {
            $listnum = 0;
            if ($v['list']['num'] > 1) {
                $listnum = $v['list']['num']-1;
                $PHPSheet
                ->mergeCells('A'.$n.':A'.($listnum+$n))
                ->mergeCells('B'.$n.':B'.($listnum+$n))
                ->mergeCells('G'.$n.':G'.($listnum+$n))
                ->mergeCells('H'.$n.':H'.($listnum+$n))
                ->mergeCells('N'.$n.':N'.($listnum+$n));
            }
            $PHPSheet
            ->setCellValue('A'.$n,$v['pay_time'])
            ->setCellValue('B'.$n,$v['receiver_name']);
            $PHPSheet->getStyle('A'.$n)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $PHPSheet->getStyle('B'.$n)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

            $j=$n;
            foreach ($v['list']['raise'] as $kk => $vv) {
                $PHPSheet
                ->setCellValue('C'.$j,$vv['goods_name'])
                ->setCellValue('D'.$j,$vv['price'])
                ->setCellValue('E'.$j,$vv['num'])
                ->setCellValue('F'.$j,$vv['price_total'])
                ->setCellValue('I'.$j,$vv['pur_price'])
                ->setCellValue('J'.$j,$vv['num'])
                ->setCellValue('K'.$j,$vv['cost_total'])
                ->setCellValue('L'.$j,$vv['diff_total']);
                $j ++;
            }

            if (sizeof($v['list']['raise'])>1) {
                $len = sizeof($v['list']['raise'])-1;
                $PHPSheet->mergeCells('M'.$n.':M'.($len+$n));
            }

            if (sizeof($v['list']['raise'])>0) {
                $PHPSheet->setCellValue('M'.$n,$v['list']['raise_total']);
                $PHPSheet->getStyle('M'.$n)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            }
            
            $nh = $j;
            $h = $nh;
            foreach ($v['list']['loss'] as $kk => $vv) {
                $PHPSheet
                ->setCellValue('C'.$h,$vv['goods_name'])
                ->setCellValue('D'.$h,$vv['price'])
                ->setCellValue('E'.$h,$vv['num'])
                ->setCellValue('F'.$h,$vv['price_total'])
                ->setCellValue('I'.$h,$vv['pur_price'])
                ->setCellValue('J'.$h,$vv['num'])
                ->setCellValue('K'.$h,$vv['cost_total'])
                ->setCellValue('L'.$h,$vv['diff_total']);
                $h++;
            }

            if (sizeof($v['list']['loss'])>1) {
                $len = sizeof($v['list']['loss'])-1;
                $PHPSheet->mergeCells('M'.$nh.':M'.($len+$nh));
            }
            if (sizeof($v['list']['loss'])>0) {
                $PHPSheet->setCellValue('M'.$nh,$v['list']['loss_total']);
                $PHPSheet->getStyle('M'.$nh)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            }

            $PHPSheet->setCellValue('G'.$n,$v['balance']);
            $PHPSheet->getStyle('G'.$n)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

            $PHPSheet->setCellValue('H'.$n,$v['agent']?:'待分配');
            $PHPSheet->getStyle('H'.$n)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

            $PHPSheet->setCellValue('N'.$n,$v['diff_total']);
            $PHPSheet->getStyle('N'.$n)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

            $n += $listnum+1;
        }

        

        // $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,'Excel2007');//按照指定格式生成Excel文件，‘Excel2007’表示生成2007版本的xlsx，‘Excel5’表示生成2003版本Excel文件
        // ob_end_clean();
        // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
        // //header('Content-Type:application/vnd.ms-excel');//告诉浏览器将要输出Excel03版本文件
        // header('Content-Disposition: attachment;filename="01simple.xlsx"');//告诉浏览器输出浏览器名称
        // header('Cache-Control: max-age=0');//禁止缓存
        // $PHPWriter->save("php://output");
        // 
         $objWriteHTML = new \PHPExcel_Writer_HTML($PHPExcel);
        // $objWriteHTML->save("php://output");
        $data['w'] = $iw;
        $data['html'] = $objWriteHTML->getHtml();
        return $this->fetch('',$data);
    }

    public function export()
    {
        $param = request()->param();
        // $ids = $param['ids'];

        $list = Db::field('*')
        ->table('sdzx_order')->alias('o')
        ->join('sdzx_order_goods g','o.order_id=g.order_id')
        // ->where("o.order_id in({$ids})")
        ->where(['pay_status'=>1])
        ->where('o.pay_time','between',[$param['start_time'],$param['end_time']])
        ->select();

        $arr = [];
        foreach ($list as $k => $v) {
            if (!isset($arr[$v['order_id']]['order_id'])) {
                $arr[$v['order_id']] = [
                    'order_id'        => $v['order_id'],
                    'order_no'        => $v['order_no'],
                    'user_id'         => $v['user_id'],
                    'pay_time'        => $v['pay_time'],
                    'receiver_name'   => $v['receiver_name'],
                    'receiver_mobile' => $v['receiver_mobile'],
                    'order_money'     => $v['order_money'],
                    'pay_money'       => $v['pay_money'],
                    'list' => ['num'=>0]
                ];
            }
            
            $ptmp = Db::name('purchase_goods')->where(['specval_id'=>$v['goods_specval_id'],'status'=>1])->order('pg_id desc')->find();
            $v['pur_price'] = $ptmp['cur_price']?:0;

            $v['price_total'] = $v['price']*$v['num'];
            $v['cost_total'] = $v['pur_price']*$v['num'];
            $v['diff_total'] = $v['price_total']-$v['cost_total'];

            $arr[$v['order_id']]['list']['num'] +=1;
            if ($v['diff_total']>0) {
                $arr[$v['order_id']]['list']['raise_total'] += $v['diff_total'];
                $arr[$v['order_id']]['list']['raise'][] = $v;
            }else{
                $arr[$v['order_id']]['list']['loss_total'] += $v['diff_total'];
                $arr[$v['order_id']]['list']['loss'][] = $v;
            }
            $arr[$v['order_id']]['diff_total'] += $v['diff_total'];
        }

        foreach ($arr as $k => $v) {
            // $v['change'] = Db::name('order_change')->where(['order_id'=>$v['order_id'],'pay_status'=>1])->select();
            $tmp = Db::field('sum(price)')->name('order_change')->where(['order_id'=>$v['order_id'],'pay_status'=>1])->value('sum(price)');

            $v['refundgoods'] = Db::field('sum(rg_num*return_money)')->name('order_goods_return')->where(['order_id'=>$v['order_id'],'pay_status'=>1])->value('sum(rg_num*return_money)');
            $v['balance'] = $v['pay_money']+($tmp-$v['refundgoods']);
            $v['agent'] = Db::field("e.name")->table('sdzx_employee')->alias('e')->join('sdzx_shop s','e.id=s.agent_id','left')->where("s.user_id={$v['user_id']}")->value("e.name");

            $arr[$k] = $v;
        }

        vendor("PHPExcel.PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.IOFactory");
        vendor('PHPExcel.PHPExcel.PHPExcel_Style_Alignment');

        $PHPExcel = new \PHPExcel(); //实例化PHPExcel类，类似于在桌面上新建一个Excel表格

        $PHPSheet = $PHPExcel->getActiveSheet(); //获得当前活动sheet的操作对象
        $PHPSheet->setTitle(date('Y-m-d')); //给当前活动sheet设置名称

        $PHPSheet->mergeCells('A1:N1');
        $PHPSheet->setCellValue('A1','订单明细导出（'.date('Y-m-d').'）');//
        // $PHPSheet->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $PHPSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $PHPSheet->getStyle('A1')->getFont()->setSize(20)->setBold(true);

        $PHPSheet->mergeCells('A2:H2');
        $PHPSheet->setCellValue('A2','订单信息');//
        $PHPSheet->getStyle('A2')->getFont()->setBold(true);
        $PHPSheet->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $PHPSheet->mergeCells('I2:L2');
        $PHPSheet->setCellValue('I2','采购信息');//
        $PHPSheet->getStyle('I2')->getFont()->setBold(true);
        $PHPSheet->getStyle('I2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $PHPSheet->setCellValue('A3','下单时间')->setCellValue('B3','收件人')->setCellValue('C3','商品名称')->setCellValue('D3','价格')->setCellValue('E3','数量')->setCellValue('F3','小计')->setCellValue('G3','总金额')->setCellValue('H3','管家')->setCellValue('I3','进价')->setCellValue('J3','数量')->setCellValue('K3','总价')->setCellValue('L3','差价（=小计-总价）')->setCellValue('M3','差价小计')->setCellValue('N3','差价总额');

        $n = 4;
        foreach ($arr as $k => $v) {
            $listnum = 0;
            if ($v['list']['num'] > 1) {
                $listnum = $v['list']['num']-1;
                $PHPSheet
                ->mergeCells('A'.$n.':A'.($listnum+$n))
                ->mergeCells('B'.$n.':B'.($listnum+$n))
                ->mergeCells('G'.$n.':G'.($listnum+$n))
                ->mergeCells('H'.$n.':H'.($listnum+$n))
                ->mergeCells('N'.$n.':N'.($listnum+$n));
            }
            $PHPSheet
            ->setCellValue('A'.$n,$v['pay_time'])
            ->setCellValue('B'.$n,$v['receiver_name']);
            $PHPSheet->getStyle('A'.$n)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $PHPSheet->getStyle('B'.$n)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

            $j=$n;
            foreach ($v['list']['raise'] as $kk => $vv) {
                $PHPSheet
                ->setCellValue('C'.$j,$vv['goods_name'])
                ->setCellValue('D'.$j,$vv['price'])
                ->setCellValue('E'.$j,$vv['num'])
                ->setCellValue('F'.$j,$vv['price_total'])
                ->setCellValue('I'.$j,$vv['pur_price'])
                ->setCellValue('J'.$j,$vv['num'])
                ->setCellValue('K'.$j,$vv['cost_total'])
                ->setCellValue('L'.$j,$vv['diff_total']);
                $j ++;
            }

            if (sizeof($v['list']['raise'])>1) {
                $len = sizeof($v['list']['raise'])-1;
                $PHPSheet->mergeCells('M'.$n.':M'.($len+$n));
            }

            if (sizeof($v['list']['raise'])>0) {
                $PHPSheet->setCellValue('M'.$n,$v['list']['raise_total']);
                $PHPSheet->getStyle('M'.$n)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            }
            
            $nh = $j;
            $h = $nh;
            foreach ($v['list']['loss'] as $kk => $vv) {
                $PHPSheet
                ->setCellValue('C'.$h,$vv['goods_name'])
                ->setCellValue('D'.$h,$vv['price'])
                ->setCellValue('E'.$h,$vv['num'])
                ->setCellValue('F'.$h,$vv['price_total'])
                ->setCellValue('I'.$h,$vv['pur_price'])
                ->setCellValue('J'.$h,$vv['num'])
                ->setCellValue('K'.$h,$vv['cost_total'])
                ->setCellValue('L'.$h,$vv['diff_total']);
                $h++;
            }

            if (sizeof($v['list']['loss'])>1) {
                $len = sizeof($v['list']['loss'])-1;
                $PHPSheet->mergeCells('M'.$nh.':M'.($len+$nh));
            }
            if (sizeof($v['list']['loss'])>0) {
                $PHPSheet->setCellValue('M'.$nh,$v['list']['loss_total']);
                $PHPSheet->getStyle('M'.$nh)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            }

            $PHPSheet->setCellValue('G'.$n,$v['balance']);
            $PHPSheet->getStyle('G'.$n)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

            $PHPSheet->setCellValue('H'.$n,$v['agent']?:'待分配');
            $PHPSheet->getStyle('H'.$n)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

            $PHPSheet->setCellValue('N'.$n,$v['diff_total']);
            $PHPSheet->getStyle('N'.$n)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

            $n += $listnum+1;
        }

        

        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,'Excel2007');//按照指定格式生成Excel文件，‘Excel2007’表示生成2007版本的xlsx，‘Excel5’表示生成2003版本Excel文件
        ob_end_clean();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
        //header('Content-Type:application/vnd.ms-excel');//告诉浏览器将要输出Excel03版本文件
        header('Content-Disposition: attachment;filename="01simple.xlsx"');//告诉浏览器输出浏览器名称
        header('Cache-Control: max-age=0');//禁止缓存
        $PHPWriter->save("php://output");

    }
}